#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
客服绑定数据导出到Excel
从网页中提取的表格数据导出为Excel文件
"""

import pandas as pd
from datetime import datetime
import os

def create_excel_report():
    """创建Excel报表"""
    
    # 从网页提取的表格数据
    table_data = [
        ["用户ID", "用户名称", "已绑定客服", "创建时间", "操作"],
        ["9", "用户-1[角色客服](勿删)", "", "2025-08-06", "编辑/删除"],
        ["8", "用户-2[角色客服](勿删)", "56565,121212", "2025-08-06", "编辑/删除"],
        ["1", "yiyouju", "65656,agent-6", "2025-08-06", "编辑/删除"]
    ]
    
    # 分离表头和数据
    headers = table_data[0]
    data_rows = table_data[1:]
    
    # 创建DataFrame
    df = pd.DataFrame(data_rows, columns=headers)
    
    # 生成文件名（包含当前时间戳）
    timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
    filename = f"客服绑定数据_{timestamp}.xlsx"
    
    # 导出到Excel
    with pd.ExcelWriter(filename, engine='openpyxl') as writer:
        # 写入主要数据
        df.to_excel(writer, sheet_name='客服绑定数据', index=False)
        
        # 获取工作表对象进行格式化
        worksheet = writer.sheets['客服绑定数据']
        
        # 自动调整列宽
        for column in worksheet.columns:
            max_length = 0
            column_letter = column[0].column_letter
            for cell in column:
                try:
                    if len(str(cell.value)) > max_length:
                        max_length = len(str(cell.value))
                except:
                    pass
            adjusted_width = min(max_length + 2, 50)  # 最大宽度限制为50
            worksheet.column_dimensions[column_letter].width = adjusted_width
    
    print(f"Excel文件已成功创建: {filename}")
    print(f"文件路径: {os.path.abspath(filename)}")
    print(f"数据行数: {len(data_rows)}")
    print(f"列数: {len(headers)}")
    
    # 显示数据概览
    print("\n数据概览:")
    print(df.to_string(index=False))
    
    return filename

if __name__ == "__main__":
    try:
        filename = create_excel_report()
        print(f"\n✅ Excel导出成功！文件名: {filename}")
    except Exception as e:
        print(f"❌ 导出失败: {str(e)}") 